package com.wyhello.tube4.dao.impl;

import java.math.BigInteger;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Session;
import org.hibernate.SessionFactory;

import com.wyhello.tube4.dao.PositionRecordsDAO;
import com.wyhello.tube4.pojo.Position;

public class PositionRecordsDAOImpl implements PositionRecordsDAO{

	private SessionFactory sessionFactory;
	public SessionFactory getSessionFactory() {
		return sessionFactory;
	}
	public void setSessionFactory(SessionFactory sessionFactory) {
		this.sessionFactory = sessionFactory;
	}
	/**
	 * 位置记录信息的录入
	 * @param position 要录入的信息
	 * @return 返回是否录入成功
	 */
	@Override
	public boolean savePosition(Position position) {
		Session session = sessionFactory.getCurrentSession();
		int i = 1;
		try{
			session.save(position);
		}catch(Exception e){
			i=0;
		}
		if(i==1){
			return true;
		}else{
			return false;
		}
	}
	/**
	 * 对位置记录的信息进行批量删除
	 * @param ids 要删除的id
	 * @return 返回删除的条数
	 */
	@Override
	public int deletePosition(String ids) {
		Session session  =  sessionFactory.getCurrentSession();
		String sql = "delete from position where id in "+ids;
		int i = session.createSQLQuery(sql).executeUpdate();
		return i;
	}
	/**
	 * 查询所有的位置记录信息
	 * @return 返回所有位置记录的信息
	 */
	@Override
	public List<Position> queryPosition() {
		Session session  =  sessionFactory.getCurrentSession();
		String hql = "from Position";
		List<Position> list = session.createQuery(hql).list();
		return list;
	}
	/**
	 * 根据id查询位置记录信息
	 * @param id 要查询的id
	 * @return 为ID的位置记录信息
	 */
	@Override
	public Position getPositionById(int id) {
		Session session  =  sessionFactory.getCurrentSession();
		Position position = (Position) session.get(Position.class, Integer.valueOf(id));
		return position;
	}
	/**
	 * 对位置记录进行修改
	 * @param position 修改后的信息
	 * @return 修改是否成功
	 */
	@Override
	public boolean updatePosition(Position position) {
		Session session  =  sessionFactory.getCurrentSession();
		try{
			session.update(position);
		}catch(Exception e){
			return false;
		}
		return true;
	}
	
	public void deletePOj(Position position){
		Session session  =  sessionFactory.getCurrentSession();
		session.delete(position);
	}
	
	
	@Override
	public Map<Integer,List<Position>> getPositionsNotCheckedThisYear(int boilerId, int count) {
		String sqlS = "FROM POSITION WHERE tubeId IN " +
				"(SELECT id FROM tube WHERE resideId IN " +
				"(SELECT id FROM reside WHERE LEVEL=3 AND parentId IN " +
				"(SELECT id FROM reside WHERE LEVEL = 2 AND parentId = " +
				"(SELECT id FROM reside WHERE LEVEL=1 AND id="+boilerId+")))) " +
				"AND id NOT IN " +
				"(SELECT positionId FROM reality WHERE DATEDIFF(NOW(),TIME)<365)";
		String sql = "SELECT * " + sqlS;
		
		if(count != 0){
			sql += "  LIMIT 0," + count;
		}
		
		Session session  =  sessionFactory.getCurrentSession();
		
		List<Position> positions = session.createSQLQuery(sql).addEntity(Position.class).list();
		
		String sqlC = "SELECT COUNT(*) " + sqlS;	
		List list = session.createSQLQuery(sqlC).list(); 
		int total =  ((BigInteger) list.iterator().next()).intValue();
		
		Map map = new HashMap();
		map.put("count", total);
		map.put("positions", positions);
		return map;
	}
	
	@Override
	public Map<Integer,List<Position>> getPositionsNotCheckedLasttime(int boilerId, int count){
		
		String sqlS = "FROM POSITION a,   " +
		" tube c, reside d, reside e, reside f " +
		" WHERE a.tubeId=c.id AND c.resideId = d.id " +
		" AND d.parentid = e.id AND e.parentid = f.id AND f.id = "+boilerId +
		" AND a.id NOT IN  " +
		" (SELECT positionId FROM macroscopic WHERE bagId=(SELECT MAX(id) FROM bag))";
		String sql = "SELECT * " + sqlS;
	
		if(count != 0){
			sql += "  LIMIT 0," + count;
		}
		
		Session session  =  sessionFactory.getCurrentSession();
		
		List<Position> positions = session.createSQLQuery(sql).addEntity(Position.class).list();
		
		String sqlC = "SELECT COUNT(*) " + sqlS;	
		List list = session.createSQLQuery(sqlC).list(); 
		int total =  ((BigInteger) list.iterator().next()).intValue();
		
		Map map = new HashMap();
		map.put("count", total);
		map.put("positions", positions);
		return map;
	}
	
	@Override
	public Map<Integer,List<Position>> getPositionNeverChecked(int boilerId, int count){
		
		String sqlS = "FROM POSITION a,   " +
				" tube c, reside d, reside e, reside f " +
				" WHERE a.tubeId=c.id AND c.resideId = d.id " +
				" AND d.parentid = e.id AND e.parentid = f.id AND f.id = "+boilerId +
				" AND a.id NOT IN  " +
				" (SELECT positionId FROM macroscopic)";
		String sql = "SELECT * " + sqlS;
				
		if(count != 0){
			sql += "  LIMIT 0," + count;
		}
				
		Session session  =  sessionFactory.getCurrentSession();
				
		List<Position> positions = session.createSQLQuery(sql).addEntity(Position.class).list();
				
		String sqlC = "SELECT COUNT(*) " + sqlS;	
		List list = session.createSQLQuery(sqlC).list(); 
		int total =  ((BigInteger) list.iterator().next()).intValue();
		
		Map map = new HashMap();
		map.put("count", total);
		map.put("positions", positions);
		return map;
	}
}
